package com.easyspringboot.bulider;

import com.easyspringboot.bean.Constants;
import com.easyspringboot.bean.FieldInfo;
import com.easyspringboot.bean.TableInfo;
import com.easyspringboot.utils.JsonUtils;
import com.easyspringboot.utils.PropertiesUtils;
import com.easyspringboot.utils.StringUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author xiaoyi
 * @Version 1.0
 * @ClassName BuliderTable
 * @Time 3:48
 */
public class BuildTable {

    private static final Logger logger = LoggerFactory.getLogger(BuildTable.class);
    private  static Connection connection = null;

    private static final String  SQL_SHOW_TABLE_STATUS = "show table status";
    private static final String  SQL_SHOW_TABLE_FIELDS = "show full fields from %s";
    private static final String  SQL_SHOW_TABLE_INDEX = "show index from %s";

    static {
        String driverName = PropertiesUtils.getProperties("db.driver.name");
        String url = PropertiesUtils.getProperties("db.url");
        String username = PropertiesUtils.getProperties("db.username");
        String password = PropertiesUtils.getProperties("db.password");

        try {
            Class.forName(driverName);
            connection = java.sql.DriverManager.getConnection(url,username,password);
            logger.info("数据库连接成功");

        } catch (Exception e) {
            logger.error("数据库连接失败",e);
        }
    }
    /**
     * 获取表信息
     * @return List<TableInfo>
     * @throws SQLException
     * @throws ClassNotFoundException
     *
     */

    public static List<TableInfo> getTables(){
        PreparedStatement ps = null;
        ResultSet tablerest = null;
        List<TableInfo> fieldInfoList = new ArrayList();
        
        try {
            ps=connection.prepareStatement(SQL_SHOW_TABLE_STATUS);
            tablerest = ps.executeQuery();

            while (tablerest.next()){
                String tablename = tablerest.getString("Name");
                String comment = tablerest.getString("Comment");

                String beanName = tablename;
                if (Constants.IGNORE_TABLE_PREFIX){
                    beanName = tablename.substring(tablename.indexOf("_")+1);
                }

                beanName = processField(beanName,true);

                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(tablename);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(comment);
                tableInfo.setBeanParamName(beanName+ Constants.SUFFIX_BEAN_QUERY);

                readFieldInfo(tableInfo);
                getKeyIndexInfo(tableInfo);
                fieldInfoList.add(tableInfo);//添加到集合中
                 logger.info("tableInfo:{}",JsonUtils.convertobj2Json(tableInfo));
//                logger.info("表：{}",JsonUtils.convertobj2Json(tableInfo));
//                logger.info("字段：{}",JsonUtils.convertobj2Json(fieldInfos));

            }

        } catch (Exception e) {
            logger.error("数据库连接失败",e);
        }finally {
            if (tablerest!=null){
                try {
                    tablerest.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }

            }
            if (ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (connection!=null){
                try {
                    connection.close();

                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return fieldInfoList;

    }
    private static void readFieldInfo(TableInfo tableInfo){

        PreparedStatement ps = null;
        ResultSet fileresult = null;
        List<FieldInfo> fieldInfoList = new ArrayList();
        List<FieldInfo> fieldExtendList = new ArrayList<>();
        try {
            ps=connection.prepareStatement(String.format(SQL_SHOW_TABLE_FIELDS,tableInfo.getTableName()));
            fileresult = ps.executeQuery();
            Boolean haveDateTime = false;
            Boolean haveDate = false;
            Boolean haveBigDecimal = false;
            while (fileresult.next()){
                String field = fileresult.getString("field");
                String type = fileresult.getString("type");
                String extra = fileresult.getString("extra");
                String comment = fileresult.getString("comment");
                if (type.indexOf("(")>0){
                    type = type.substring(0,type.indexOf("("));
                }
                String  propertyName = processField(field,false);
                FieldInfo fieldInfo = new FieldInfo();
                fieldInfoList.add(fieldInfo);
                fieldInfo.setFiledName(field);
                fieldInfo.setSqlType(type);
                tableInfo.setFieldList(fieldInfoList);
                fieldInfo.setComment(comment);
                fieldInfo.setAutoIncrement("auto_increment".equalsIgnoreCase(extra));
                fieldInfo.setPropertyName(propertyName);
                fieldInfo.setJavaType(processJavaType(type));
                if (ArrayUtils.contains(Constants.SQL_DATA_TIME_TYPES,type)){
                    haveDateTime = true;
                }
                if (ArrayUtils.contains(Constants.SQL_DATE_TYPES,type)){
                    haveDate = true;
                }
                if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPES,type)){
                    haveBigDecimal = true;
                }
                if (ArrayUtils.contains(Constants.SQL_STRING_TYPES,type)){
                    FieldInfo fuzzyField= new FieldInfo();
                    fuzzyField.setJavaType(fieldInfo.getJavaType());
                    fuzzyField.setPropertyName(propertyName+Constants.SUFFIX_BEAN_QUERY_FUZZY);
                    fuzzyField.setFiledName(fieldInfo.getFiledName());
                    fuzzyField.setSqlType(type);
                    fieldExtendList.add(fuzzyField);
                }
                if (ArrayUtils.contains(Constants.SQL_DATA_TIME_TYPES,type)||ArrayUtils.contains(Constants.SQL_DATE_TYPES,type)){
                    FieldInfo timeStartField= new FieldInfo();
                    timeStartField.setJavaType("String");
                    timeStartField.setFiledName(fieldInfo.getFiledName());
                    timeStartField.setPropertyName(fieldInfo.getPropertyName()+Constants.SUFFIX_BEAN_QUERY_TIME_START);
                    timeStartField.setSqlType(type);
                    fieldExtendList.add(timeStartField);

                    FieldInfo timeEndField= new FieldInfo();
                    timeEndField.setJavaType("String");
                    timeEndField.setFiledName(fieldInfo.getFiledName());
                    timeEndField.setPropertyName(fieldInfo.getPropertyName()+Constants.SUFFIX_BEAN_QUERY_TIME_END);
                    timeEndField.setSqlType(type);
                    fieldExtendList.add(timeEndField);
                }
                logger.info("field:{},type:{},setPropertyName:{},extra:{},comment:{},javaType:{}",field,type,propertyName,extra,comment,fieldInfo.getJavaType());

            }

            tableInfo.setHaveDateTime(haveDateTime);
            tableInfo.setHavaDate(haveDate);
            tableInfo.setHaveBigDecimal(haveBigDecimal);
            tableInfo.setFieldList(fieldInfoList);
            tableInfo.setFieldExtendList(fieldExtendList);

        } catch (Exception e) {
            logger.error("数据库连接失败",e);
        }finally {
            if (fileresult!=null){
                try {
                    fileresult.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }

            }
            if (ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

        }

    }


    private static List<FieldInfo> getKeyIndexInfo(TableInfo tableInfo){

        PreparedStatement ps = null;
        ResultSet fileresult = null;
        List<FieldInfo> fileInfoList = new ArrayList();
        try {
            Map<String,FieldInfo> tempMap = new HashMap();
            for (FieldInfo fieldInfo:tableInfo.getFieldList()){
                tempMap.put(fieldInfo.getFiledName(),fieldInfo);
            }
            ps=connection.prepareStatement(String.format(SQL_SHOW_TABLE_INDEX,tableInfo.getTableName()));
            fileresult = ps.executeQuery();

            while (fileresult.next()) {
                String keyName = fileresult.getString("key_name");
                Integer nonUnique = fileresult.getInt("non_unique");
                String columnName = fileresult.getString("column_name");
                if (nonUnique==1){
                    continue;
                }
                List<FieldInfo> keyFieldList = tableInfo.getKeyIndexMap().get(keyName);
                if(null==keyFieldList){
                    keyFieldList = new ArrayList();
                    tableInfo.getKeyIndexMap().put(keyName,keyFieldList);
                }
//                for (FieldInfo fieldInfo:tableInfo.getFieldList()){
//                    if (fieldInfo.getFieldName().equals(columnName)){
//                        keyFieldList.add(fieldInfo);
//                        break;
//                    }
//                }
                keyFieldList.add(tempMap.get(columnName));

            }
        } catch (Exception e) {
            logger.error("读取索引失败",e);
        }finally {
            if (fileresult!=null){
                try {
                    fileresult.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }

            }
            if (ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

        }

        return fileInfoList;
    }

    private static String processField(String field,boolean uperCaseFirstLetter) {

        StringBuffer sb = new StringBuffer();
        String[] fields = field.split("_");
        sb.append(uperCaseFirstLetter ? StringUtils.uperCaseFirstLetter(fields[0]) : fields[0]);
        for (int i = 1; i < fields.length; i++) {
            sb.append(StringUtils.uperCaseFirstLetter(fields[i]));
        }
    return sb.toString();
    }
    private static String processJavaType(String sqlType) {
        if (ArrayUtils.contains(Constants.SQL_STRING_TYPES, sqlType)) {
            return "String";
        } else if (ArrayUtils.contains(Constants.SQL_INTEGER_TYPES, sqlType)) {
            return "Integer";
        } else if (ArrayUtils.contains(Constants.SQL_LONG_TYPES, sqlType)) {
            return "Long";
        } else if (ArrayUtils.contains(Constants.SQL_DOUBLE_TYPES, sqlType)) {
            return "Double";
        } else if (ArrayUtils.contains(Constants.SQL_FLOAT_TYPES, sqlType)) {
            return "Float";
        } else if (ArrayUtils.contains(Constants.SQL_DATE_TYPES, sqlType)||ArrayUtils.contains(Constants.SQL_DATA_TIME_TYPES, sqlType)) {
            return "Date";
        } else if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPES, sqlType)) {
            return "BigDecimal";
        }else{
            throw new RuntimeException("未知的类型："+sqlType);
        }
    }

}
